#include <stdio.h>
#include <stdlib.h>
#include <sqlite3.h>

#include <sys/types.h> 
#include <sys/socket.h>
#include <netinet/in.h>
#include <arpa/inet.h>
#include <string.h>
#include <unistd.h>
#include <signal.h> //假如信号函数的头文件，以解决僵尸进程问题


#define DBNAME "emply.db"
#define TABLE "emplyformation"     //数据库里面的一个表

void clientUpdateData(sqlite3 *db,char str[7][128]);
const char *loginConfirmation(sqlite3 *db,char str[7][128]);
const char *returnMsg(sqlite3 *db,char str[7][128]);
int systemRun(int argc, char const *argv[],sqlite3 *db);
void databasePreserve(sqlite3 *db);
void sqliteInsert(sqlite3 *db);
void sqliteSelect(sqlite3 *db);
int callback(void *arg,int column,char **text,char **name);
void sqliteDelete(sqlite3 *db);
void sqliteUpdate(sqlite3 *db);
void sqliteGetTable(sqlite3 *db);

int flags = 0;

//有关服务器定义及声明起
#define N 128

//因为多次键入perror和exit,所以在这里定义一个函数宏
#define ERRLOG(errormsg) do{\
                            perror(errormsg);\
                            printf("%s - %s - %d\n",__FILE__,__func__,__LINE__);\
                            exit(1);\
                          }while(0)

void handler(int sig)
{
    wait(NULL);
}
//有关服务器的定义及声明止

//由于数据库是由第三方提供的，所以要手动链接第三方动态库

int main(int argc, char const *argv[])
{
    int password;                                                                                   
    printf("-------\n");
    printf("-------------\n");
    printf("-------------------\n");
    printf("-------------------------\n");
    printf("----欢迎使用员工管理系统----\n");
    printf("-------------------------\n");
    printf("-------------------\n");
    printf("-------------\n");  
    printf("-------\n"); 
    while(1)
    {
        printf("请输入密码>>>");
  
        if(scanf("%d",&password) != 1)
        {
            printf("您的输入有误，请重新输入！\n");
            while (getchar() != '\n'){}
            continue;            
        }else
        {
            if(password == 123456)
            {
                break;
            }
            else
            {
                printf("您输入的密码有误，请重新输入！\n");
                while (getchar() != '\n'){}  
            }
        }
    }

    sqlite3 *db;
    //打开或者创建一个数据库
    if(sqlite3_open(DBNAME,&db) != SQLITE_OK)
    {
        printf("错误：%s\n",sqlite3_errmsg(db));
        exit(1);
    }
    else
    {
        printf("成功：数据库打开或创建成功！\n");
    }

    //创建表
    char sql[128] = {0};
    char *errmsg;
    sprintf(sql,"create table %s(worknum int primary key,name char,sex char,birth char,post char,salary int,password char)", TABLE);
    if(sqlite3_exec(db,sql,NULL,NULL,&errmsg) != SQLITE_OK)
    {
        printf("错误：%s\n",errmsg);
    }
    else
    {
        printf("成功：数据表创建成功了！\n");
    }

    //系统操作选择表
    int select1;
    while(1)
    {
        printf("----------------------------------\n");
        printf("----1.数据维护 2.系统运行 3.退出----\n");
        printf("----------------------------------\n");
        printf(">>>");
        if(scanf("%d",&select1) != 1)
        {
           printf("您的输入有误，请重新输入！\n");
           while (getchar() != '\n'){}
           continue;            
        }
 
        switch (select1)
        {
        case 1:
            databasePreserve(db);
            break;
        case 2:
            if(systemRun(argc,argv,db) == 1)
            {
                exit(1);
            }
            break;
        case 3:
            exit(1);    
            break;
        default:
            printf("您的输入有误，请重新输入！\n");
            break;
        }     
    }
    return 0;    
}


void databasePreserve(sqlite3 *db)
{
    //数据库操作表
    int sum;
    while(1)
    {
        printf("----------------------------------------------------------\n");
        printf("----1.插入数据 2.查询数据 3.修改数据 4.删除数据 5.返回上一级----\n");
        printf("----------------------------------------------------------\n");
        printf(">>>");

        if(scanf("%d",&sum) != 1)
        {
            printf("您的输入有误，请重新输入！\n");
            while (getchar() != '\n'){}
            continue;            
        }

        switch (sum)
        {
        case 1:
            sqliteInsert(db);
            break;
        case 2:
//            sqliteSelect(db); //页可以用下面另一个命令完成
            sqliteGetTable(db);
            break;
        case 3:
            sqliteUpdate(db);
            break;
        case 4:
            sqliteDelete(db);
            break;
        case 5:
            sqlite3_close(db);
//            exit(1);    
            break;
        default:
            printf("您的输入有误，请重新输入！\n");
            break;
        }
        if(sum == 5)
        {
            break;
        }
    }
}

void sqliteInsert(sqlite3 *db)
{
    int worknum;
    char name[32];
    char sex;
    char birth[32];
    char post[32];
    int salary;
    char password[32];
    char sql[128] = {0};
    char *errmsg;
    printf("请输入工号：");
    scanf("%d",&worknum);
    printf("worknum = %d\n",worknum);
    printf("请输入姓名：");
    scanf("%s",name);
    printf("name = %s\n",name);
    printf("请输入性别：");
    getchar();
    scanf("%c",&sex);
    printf("sex = %c\n",sex);
    printf("请输入出生年月：");
    scanf("%s",birth);
    printf("birth = %s\n",birth);
    printf("请输入职务：");
    scanf("%s",post);
    printf("post = %s\n",post);
    printf("请输入薪资：");
    scanf("%d",&salary);
    printf("salary = %d\n",salary);
    printf("请输入密码：");
    scanf("%s",password);
    printf("password = %s\n",password);

    sprintf(sql,"insert into %s values(%d,'%s','%c','%s','%s',%d,'%s')",TABLE,worknum,name,sex,birth,post,salary,password);
    //如果字段值是字符或者字符串要加单引号或双引号，此处加单引号比较方便，如果加双引号得转换一下
    //则用\转换一下。如：\"%s\"。
    if(sqlite3_exec(db,sql,NULL,NULL,&errmsg) != SQLITE_OK)
    {
        printf("错误：%s\n",errmsg);
    }
    else
    {
        printf("成功：插入成功！\n");
    }
}
/*
//sqlite3_exec的回调函数会调用多次，会根据记录的操作决定调用的次数
int callback(void *arg,int column,char **text,char **name)
{
    //下面三行是测试
//    printf("column = %d\n",column);
//    printf("text[0] = %s;text[1] = %s\n",text[0],text[1]);
//    printf("name[0] = %s;name[1] = %s\n",name[0],name[1]);
    int i;

    if(flags == 0)
    {
        for(i = 0;i < column;i++)
        {
            printf("%-11s",name[i]);
        }
        putchar(10);
        puts("-----------------------------------------");
        flags = 1;
    }

    for(i = 0;i < column;i++)
    {
        printf("%-11s",text[i]);
    }
    putchar(10);

    //sqlite3_exec的回调函数执行完毕后一定返回0，否则会报错
    return 0;
}

void sqliteSelect(sqlite3 *db)
{
    char sql[128] = {0};
    char *errmsg;
    flags = 0;
//    void *arg;
    sprintf(sql,"select * from %s",TABLE);
    if(sqlite3_exec(db,sql,callback,NULL,&errmsg) != SQLITE_OK)
    {
        printf("错误：%s\n",errmsg);
    }
    else
    {
        printf("成功：查询成功\n");
    }
}
*/

void sqliteDelete(sqlite3 *db)
{
    int worknum;
    char sql[128] = {};
    char *errmsg;
    printf("input 工号:");
    scanf("%d",&worknum);
    getchar();

    sprintf(sql,"delete from %s where worknum = %d",TABLE,worknum);
    if(sqlite3_exec(db,sql,NULL,NULL,&errmsg) != SQLITE_OK)
    {
        printf("错误：%s\n",errmsg);
    }
    else
    {
        printf("成功：删除成功\n");
    }
}

void sqliteUpdate(sqlite3 *db)
{
    int worknum;
    char name[32];
    char sex;
    char birth[32];
    char post[32];
    int salary;
    char password[32];

    int select2;
    char sql[128] = {};
    char *errmsg;

    printf("输入要更改的记录的工号:");
    scanf("%d",&worknum);
    getchar();
    while(1)                                                                                   
    {
        printf("----------------------选择要更改的记录--------------------\n");
        printf("----1.姓名 2.性别 3.出生年月 4.职务 5.薪资 6.密码 7.返回----\n");
        printf("----------------------------------------------------------\n");
        printf(">>>");

        if(scanf("%d",&select2) != 1)
        {
            printf("您的输入有误，请重新输入！\n");
            while (getchar() != '\n'){}
            continue;            
        }

        switch (select2)
        {
        case 1:
            printf("请输入姓名：");                                                                    
            scanf("%s",name);
            sprintf(sql,"update %s set name = %s where worknum = %d",TABLE,name,worknum);              
            break;
        case 2:
            printf("请输入性别：");
            getchar();
            scanf("%c",&sex);
            sprintf(sql,"update %s set sex = %c where worknum = %d",TABLE,sex,worknum);      
            break;
        case 3:           
            printf("请输入出生年月：");
            scanf("%s",birth);
            sprintf(sql,"update %s set birth = %s where worknum = %d",TABLE,birth,worknum);      
            break;                                                                             
        case 4:           
            printf("请输入职务：");
            scanf("%s",post);
            sprintf(sql,"update %s set post = %s where worknum = %d",TABLE,post,worknum);      
            break;                                                                             
        case 5:           
            printf("请输入薪资：");
            scanf("%d",&salary);
            sprintf(sql,"update %s set salary = %d where worknum = %d",TABLE,salary,worknum);      
            break;                                                                             
        case 6:           
            printf("请输入密码：");
            scanf("%s",password);
            sprintf(sql,"update %s set password = %s where worknum = %d",TABLE,password,worknum);
            break;
        case 7:
            break;
        default:                                                                               
            printf("您的输入有误，请重新输入！\n");
            break;
        }
        if(select2 == 7)
        {
            break;
        }

        if(sqlite3_exec(db,sql,NULL,NULL,&errmsg) != SQLITE_OK)
        {
            printf("错误：%s\n",errmsg);
        }
        else
        {
            printf("成功：修改成功\n");
        }
    }
}

void sqliteGetTable(sqlite3 *db)
{
    char sql[128] = {0};
    char **ret; //注：二级指针实质是指针数组，指针数值是一维数组
    int nrow, ncolumn;
    char *errmsg;

//    sprintf(sql,"select * from %s", TABLE);
//上面那个语句是显示所有内容，下面这个语句是显示满足条件的内容
    sprintf(sql,"select * from %s where worknum = 1002", TABLE);


//当查询某一条记录是否存在时，调用select语句是不会报错的，
//所以如果使用sqlite_exec函数很难得到是否存在的结果，
//使用sqlite_get_table函数会非常方便，可以通过它的第四个
//参数判断这条记录是否存在，如果>0就存在，如果等于0就不存在。

    if(sqlite3_get_table(db,sql,&ret,&nrow,&ncolumn,&errmsg) != SQLITE_OK)
    {
        printf("错误：%s\n",errmsg);
    }

    printf("nrow = %d,ncolumn = %d\n",nrow,ncolumn);
    
    
    int i,j,n = 0;
    //因为get_table获取的行数不包括字节名那行，但显示时要显示这行，所以+1
    for(i = 0;i < nrow+1;i++) 
    {
        for(j = 0;j < ncolumn;j++)
        {
    //对于指针数组（一维数组）必须按照一维数组的方式进行显示，即ret[n++]；
    //而不能像ret[i][j]这样显示，这是二位数组的显示方式。
            printf("%-11s",ret[n++]);
        }
        putchar(10);
    }
}

int systemRun(int argc, char const *argv[], sqlite3 *db)
{
//以下是服务器的建立以及与客户端的连接起
    if(argc != 3)
    {
        fprintf(stderr,"Usage: %s, <ip>, <port>\n",argv[0]);
        return 1;
//        exit(1);
    }
    //创建套接字，返回文件描述符，这个文件描述符后面会用到，所以要定义一下
    int sockfd;
    struct sockaddr_in serveraddr,clientaddr;
    socklen_t addrlen = sizeof(serveraddr);

    //创建套接字
    if((sockfd = socket(AF_INET,SOCK_STREAM,0)) == -1)
    {
        ERRLOG("socket error");
    }

    //填充服务器网络信息结构体
    serveraddr.sin_family = AF_INET;
    serveraddr.sin_addr.s_addr = inet_addr(argv[1]);
    serveraddr.sin_port = htons(atoi(argv[2]));


    //第三步：进行bind绑定，即将套接字与网络信息结构体绑定，
    if(bind(sockfd,(struct sockaddr *)&serveraddr,sizeof(serveraddr)) == -1)
    {
        ERRLOG("bind error");
    }

    //第四步：将套接字设置为被动接听状态
    if(listen(sockfd,5) == -1)
    {
        ERRLOG("listen error");
    }

    //-------------------------使用多进程实现TCP的并发服务器---------
    pid_t pid;
    char buf[N] = {0};
    ssize_t bytes;
    int acceptfd;
    
    //只要有客户端退出，则对应的服务器端的进程也要结束
    //就会产生僵尸进程，应该通过信号结合wait；来处理僵尸进程
    signal(SIGUSR1,handler);
    
    while(1)
    {
        //阻塞登录客户端的链接
        if((acceptfd = accept(sockfd,(struct sockaddr *)&clientaddr,&addrlen)) == -1)
        {
            ERRLOG("accept error");
        }

        printf("客户端[%s::%d]连接了\n",inet_ntoa(clientaddr.sin_addr),ntohs(clientaddr.sin_port));

        //只要有客户端连接就创建一个子进程
        if((pid = fork()) == -1)
        {
            ERRLOG("fork error");
        }
        else if(pid > 0)    //父进程继续回到accept等待连接
        {
        //    waitpid(-1,NULL,WNOHANG);
            //利用非阻塞等待函数解决子进程的僵尸问题，当然，解决结果是
            //一个子进程退出，还是会出现一个僵尸进程，再一个子进程退出，又出现一个僵尸进程
            //不过这时一个新的进程产生，就减少一个僵尸进程，再一个新进程产生，又减少一个...
            //出现这种现象的原因是父进程一直阻塞在accept处，只有新的进程来了，才能打开阻塞，往下执行
            //到达waitpid处，所以新产生一个新进程只能解决一个僵尸进程。所以，waitpid不是解决僵尸进程的好办法
            //解决僵尸进程的好办法是信号灯。
        }
        else    //子进程负责与对应的客户端通讯
        {
            while (1)
            {

                close(sockfd);
                if((bytes = recv(acceptfd,buf,N,0)) == -1)
                {
                    ERRLOG("recv error");
                }
                else if(bytes == 0)
                {
                    printf("客户端[%s::%d]退出了\n",inet_ntoa(clientaddr.sin_addr),ntohs(clientaddr.sin_port));
                    close(acceptfd);
                    kill(getppid(),SIGUSR1);
                    exit(0);
                }

                if(strcmp(buf,"quit") == 0)
                {
                    printf("客户端[%s::%d]退出了\n",inet_ntoa(clientaddr.sin_addr),ntohs(clientaddr.sin_port));
                    close(acceptfd);
                    kill(getppid(),SIGUSR1);
                    exit(0);
                }

                printf("客户端[%s::%d]:%s\n",inet_ntoa(clientaddr.sin_addr),ntohs(clientaddr.sin_port),buf);

                printf("buf = %s\n",buf);
                printf("buf的空间大小：%ld\n",sizeof(buf));
                printf("buf的字符长度：%ld\n",strlen(buf));

                printf("buf的第一个字节：%c\n",buf[0]);

                char str1[7][N] = {0};
                int i = 0,j = 0,k = 0;
                do
                {
                    str1[j][k] = buf[i];
                    i++;
                    k++;
                    if((buf[i] == '^')&&(buf[i+1] == '@')&&(buf[i+2] == '^'))
                    {
                        printf("str1[%d] = %s\n",j,str1[j]);
                        j++;
                        i = i+3;
                        k = 0;
                    }
                    printf("i = %d;j = %d;k = %d\n",i,j,k);
                }while((j < 7) && (i < strlen(buf)));

                printf("buf = %s\n",buf);

                char g = buf[0];

                printf("g = %c\n",g);
                memset(buf,0,sizeof(buf));

                switch (g)
                {
                    case '1':
                    //查询表中是否有工号和密码都符合的记录吗？如有则确认登录
                        strcpy(buf,loginConfirmation(db,str1));
                        printf("buf = %s\n",buf);
                        if(send(acceptfd,buf,N,0) == -1)
                        {
                            ERRLOG("send error");
                        }
                        break;
                    case '2':
                    //暂时无用
                        break;
                    case '3':
                    //客户端在读取数据，依据工号给客户端返回数据
                        strcpy(buf,returnMsg(db,str1));
                        printf("buf = %s\n",buf);
                        if(send(acceptfd,buf,N,0) == -1)
                        {
                            ERRLOG("send error");
                        }

                        break;
                    case '4':
                    //接收到客户端发来的修改后信息，存储到数据库中
                        clientUpdateData(db,str1);
                        break;
                    default:
                        break;
                }

                memset(buf,0,sizeof(buf));
            }
        }
    }

//服务器的建立以及与客户端的连接止
}

//下面与客户端网络通信时用到的函数
//登录时的工号与密码的确认的函数
const char *loginConfirmation(sqlite3 *db,char str[7][128])
{
    static char buf[N] = {0};
    char sql[128] = {0};
    char **ret; //注：二级指针实质是指针数组，指针数值是一维数组
    int nrow, ncolumn;
    char *errmsg;
    printf("str[1] = %s\n",str[1]);
    printf("str[2] = %s\n",str[2]);
    sprintf(sql,"select * from %s where worknum = %s and password = %s", TABLE,str[1],str[2]);
    printf("sql = %s\n",sql);
    if(sqlite3_get_table(db,sql,&ret,&nrow,&ncolumn,&errmsg) != SQLITE_OK)
    {
        printf("错误：%s\n",errmsg);     
    }
    printf("nrow = %d\n",nrow);

    if(nrow < 1)
    {
        strcpy(buf,"2^");
    }
    else
    {
        strcpy(buf,"1^");    
        strcat(buf,ret[7]);                                                                      
        strcat(buf,"^");                                                                      
        strcat(buf,ret[8]);                                                                      
        strcat(buf,"^");                                                                      
        strcat(buf,ret[9]);                                                                      
        strcat(buf,"^");                                                                      
        strcat(buf,ret[10]);                                                                    
        strcat(buf,"^");                                                                      
        strcat(buf,ret[11]);                                                                    
        strcat(buf,"^");                                                                      
        strcat(buf,ret[12]);                                                                    
        strcat(buf,"^");                                                                      
        strcat(buf,ret[13]);                                                                    
        strcat(buf,"^");
    }
    printf("buf(function) = %s\n", buf);    
    return buf;
}
//下面是客户端修改信息函数
void clientUpdateData(sqlite3 *db,char str[7][128])
{
    char sql[128] = {0};                                                                        
    char *errmsg;
    printf("str[1] = %s\n",str[1]);
    printf("str[2] = %s\n",str[2]);
    sprintf(sql,"update %s set name = '%s',sex = '%s',birth = '%s' where worknum = %s",TABLE,str[2],str[3],str[4],str[1]);
    printf("sql = %s\n",sql);
    if(sqlite3_exec(db,sql,NULL,NULL,&errmsg) != SQLITE_OK)                                
    {
        printf("错误：%s\n",errmsg);
    }
    else
    {
        printf("成功：修改成功\n");
    }
}

//只返回客户端读取的信息                                                                     
const char *returnMsg(sqlite3 *db,char str[7][128])
{
    static char buf[N] = {0};
    char sql[128] = {0};
    char **ret; //注：二级指针实质是指针数组，指针数值是一维数组
    int nrow, ncolumn;
    char *errmsg;
    printf("str[1] = %s\n",str[1]);
    sprintf(sql,"select * from %s where worknum = %s", TABLE,str[1]);
    printf("sql = %s\n",sql);
    if(sqlite3_get_table(db,sql,&ret,&nrow,&ncolumn,&errmsg) != SQLITE_OK)
    {
        printf("错误：%s\n",errmsg);     
    }
    printf("nrow = %d\n",nrow);
                                                                                               
    if(nrow < 1)
    {
        strcpy(buf,"2^");
    }
    else
    {
        strcpy(buf,"3^");    
        strcat(buf,ret[7]);                                                                    
        strcat(buf,"^");                                                                      
        strcat(buf,ret[8]);                                                                    
        strcat(buf,"^");                                                                      
        strcat(buf,ret[9]);                                                                    
        strcat(buf,"^");                                                                      
        strcat(buf,ret[10]);                                                                   
        strcat(buf,"^");                                                                      
        strcat(buf,ret[11]);     
        strcat(buf,"^");
        strcat(buf,ret[12]);
        strcat(buf,"^");
        strcat(buf,ret[13]);
        strcat(buf,"^");
    }
    printf("buf(function) = %s\n", buf);
    return buf;
}

